Python Oracle

This section shows you how to access the Oracle Database from Python using the cx_Oracle module.

The cx_Oracle is designed to conform to the Python database API 2.0 specification. It also provides you with a number of additions designed specifically for the Oracle Database.

The cx_Oracle 7.x works perfectly fine with Python version 2.7, and with versions from 3.5 to 3.7.  In this section, we will only use the Python version from 3.5 or later. The cx_Oracle can work with Oracle 11.2, 12.1, 12.2, 18.3, and 19.3 client libraries.

We assume that you already know Python programming. If you don’t, we recommend the Python tutorial.

Setting up sample tables

We’ll create two tables billing_headers and billing_items for the demonstration.

Creating the billing_headers table:

CREATE TABLE billing_headers(
    billing_no NUMBER GENERATED BY DEFAULT AS IDENTITY,
    billing_date DATE NOT NULL,
    amount NUMBER(19,4) DEFAULT 0 NOT NULL,
    customer_id NUMBER NOT NULL,
    note VARCHAR2(100),
    PRIMARY KEY(billing_no)
);Code language: SQL (Structured Query Language) (sql)

Creating the billing_items table:

CREATE TABLE billing_items(
    item_no NUMBER 
        GENERATED BY DEFAULT AS IDENTITY 
        START WITH 10 
        INCREMENT BY 10,
    billing_no NUMBER NOT NULL,
    product_id NUMBER NOT NULL,
    price NUMBER(10,2) DEFAULT 0 NOT NULL,
    PRIMARY KEY(item_no, billing_no),
    FOREIGN KEY(billing_no) 
        REFERENCES billing_headers(billing_no)
);Code language: SQL (Structured Query Language) (sql)

Python Oracle Tutorials

Was this tutorial helpful?